﻿using System;
using System.Data;
using System.Data.SqlClient;
using Entities;

namespace DAL
{
    public class DAL_News
    {
        private const string usp_SelectHotNews      = "usp_SelectHotNews";
        private const string usp_SelectNewsTop      = "usp_SelectNewsTop";
        private const string usp_SelectNews         = "usp_SelectNews";
        private const string usp_SelectNewsByID     = "usp_SelectNewsByID";
        private const string usp_SelectGroupNews    = "usp_SelectGroupNews";
        private const string usp_SelectNewsDetail   = "usp_SelectNewsDetail";

        private const string usp_InsertNews         = "usp_InsertNews";
        private const string usp_UpdateNews         = "usp_UpdateNews";
        private const string usp_UpdateNewsNotImage = "usp_UpdateNewsNotImage";
        private const string usp_DeleteNews         = "usp_DeleteNews";

        private const Int32  OK                     = 1;
        private const Int32  NOT_OK                 = -1;

        public DataTable Get_HotNews()
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlCommand sqlCommand = new SqlCommand(usp_SelectHotNews, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return dt;
            }
            return dt;
        }

        public DataTable Get_NewsTop()
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlCommand sqlCommand = new SqlCommand(usp_SelectNewsTop, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return dt;
            }
            return dt;
        }

        public DataTable Get_News_All()
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlCommand sqlCommand = new SqlCommand(usp_SelectNews, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return dt;
            }
            return dt;
        }

        public DataTable Get_News_ByID(int p_news_id)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlParameter[] sqlParameter = new SqlParameter[1];
            SqlCommand sqlCommand = new SqlCommand(usp_SelectNewsByID, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].SqlDbType = SqlDbType.BigInt;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@News_id";
            sqlParameter[0].Value = p_news_id;

            sqlCommand.Parameters.AddRange(sqlParameter);
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return dt;
            }
            return dt;
        }

        public DataSet Get_NewsDetail(int p_group_id,int p_news_id)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter sqlDataAdapter;
            SqlParameter[] sqlParameter = new SqlParameter[2];
            SqlCommand sqlCommand = new SqlCommand(usp_SelectNewsDetail, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].SqlDbType = SqlDbType.BigInt;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@Group_id";
            sqlParameter[0].Value = p_group_id;

            sqlParameter[1] = new SqlParameter();
            sqlParameter[1].SqlDbType = SqlDbType.BigInt;
            sqlParameter[1].Direction = ParameterDirection.Input;
            sqlParameter[1].ParameterName = "@News_id";
            sqlParameter[1].Value = p_news_id;

            sqlCommand.Parameters.AddRange(sqlParameter);
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(ds);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return ds;
            }
            return ds;
        }

        public DataSet Get_NewsByGroupID(int p_group_id)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter sqlDataAdapter;
            SqlParameter[] sqlParameter = new SqlParameter[1];
            SqlCommand sqlCommand = new SqlCommand(usp_SelectGroupNews, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].SqlDbType = SqlDbType.BigInt;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@Group_id";
            sqlParameter[0].Value = p_group_id;           

            sqlCommand.Parameters.AddRange(sqlParameter);
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(ds);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return ds;
            }
            return ds;
        }

        public bool DeleteNews(int p_news_id)
        {
            SqlParameter[] sqlParameter = new SqlParameter[1];
            SqlCommand sqlCommand = new SqlCommand(usp_DeleteNews, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].SqlDbType = SqlDbType.BigInt;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@News_id";
            sqlParameter[0].Value = p_news_id;
            sqlCommand.Parameters.AddRange(sqlParameter);
            try
            {
                Utils.sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return false;
            }
            return true;
        }

        public Int32 InsertNews(News pNews)
        {            
            Int32 _iNews_id = 0;

            SqlParameter[] sqlParameter = new SqlParameter[8];
            SqlCommand sqlCommand = new SqlCommand(usp_InsertNews, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].SqlDbType = SqlDbType.NVarChar;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@Title";
            sqlParameter[0].Value = pNews.TITLE;            

            sqlParameter[1] = new SqlParameter();
            sqlParameter[1].SqlDbType = SqlDbType.NText;
            sqlParameter[1].Direction = ParameterDirection.Input;
            sqlParameter[1].ParameterName = "@Content_desc";
            sqlParameter[1].Value = pNews.CONTENT_DESC;

            sqlParameter[2] = new SqlParameter();
            sqlParameter[2].SqlDbType = SqlDbType.NText;
            sqlParameter[2].Direction = ParameterDirection.Input;
            sqlParameter[2].ParameterName = "@Content_detail";
            sqlParameter[2].Value = pNews.CONTENT_DETAIL;

            sqlParameter[3] = new SqlParameter();
            sqlParameter[3].SqlDbType = SqlDbType.BigInt;
            sqlParameter[3].Direction = ParameterDirection.Input;
            sqlParameter[3].ParameterName = "@Group_id";
            sqlParameter[3].Value = pNews.GROUP_ID;

            sqlParameter[4] = new SqlParameter();
            sqlParameter[4].SqlDbType = SqlDbType.Char;
            sqlParameter[4].Direction = ParameterDirection.Input;
            sqlParameter[4].ParameterName = "@IsHotnews";
            sqlParameter[4].Value = pNews.ISHOTNEWS;

            sqlParameter[5] = new SqlParameter();
            sqlParameter[5].SqlDbType = SqlDbType.Int;
            sqlParameter[5].Direction = ParameterDirection.Input;
            sqlParameter[5].ParameterName = "@User_id";
            sqlParameter[5].Value = pNews.USER_ID;

            sqlParameter[6] = new SqlParameter();
            sqlParameter[6].SqlDbType = SqlDbType.Image;
            sqlParameter[6].Direction = ParameterDirection.Input;
            sqlParameter[6].ParameterName = "@Image_data";
            sqlParameter[6].Value = pNews.IMAGE_DATA;

            sqlParameter[7] = new SqlParameter();
            sqlParameter[7].SqlDbType = SqlDbType.Int;
            sqlParameter[7].Direction = ParameterDirection.Output;
            sqlParameter[7].ParameterName = "@News_id";            

            sqlCommand.Parameters.AddRange(sqlParameter);
            try
            {
                Utils.sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                Utils.sqlConnection.Close();
                _iNews_id = Int32.Parse(sqlParameter[7].Value.ToString());
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return NOT_OK;
            }
            return _iNews_id;
        }

        public bool UpdateNews(News pNews)
        {
            SqlParameter[] sqlParameter = new SqlParameter[8];
            SqlCommand sqlCommand = new SqlCommand(usp_UpdateNews, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].SqlDbType = SqlDbType.NVarChar;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@Title";
            sqlParameter[0].Value = pNews.TITLE;

            sqlParameter[1] = new SqlParameter();
            sqlParameter[1].SqlDbType = SqlDbType.NText;
            sqlParameter[1].Direction = ParameterDirection.Input;
            sqlParameter[1].ParameterName = "@Content_desc";
            sqlParameter[1].Value = pNews.CONTENT_DESC;

            sqlParameter[2] = new SqlParameter();
            sqlParameter[2].SqlDbType = SqlDbType.NText;
            sqlParameter[2].Direction = ParameterDirection.Input;
            sqlParameter[2].ParameterName = "@Content_detail";
            sqlParameter[2].Value = pNews.CONTENT_DETAIL;

            sqlParameter[3] = new SqlParameter();
            sqlParameter[3].SqlDbType = SqlDbType.BigInt;
            sqlParameter[3].Direction = ParameterDirection.Input;
            sqlParameter[3].ParameterName = "@Group_id";
            sqlParameter[3].Value = pNews.GROUP_ID;

            sqlParameter[4] = new SqlParameter();
            sqlParameter[4].SqlDbType = SqlDbType.Char;
            sqlParameter[4].Direction = ParameterDirection.Input;
            sqlParameter[4].ParameterName = "@IsHotnews";
            sqlParameter[4].Value = pNews.ISHOTNEWS;

            sqlParameter[5] = new SqlParameter();
            sqlParameter[5].SqlDbType = SqlDbType.Int;
            sqlParameter[5].Direction = ParameterDirection.Input;
            sqlParameter[5].ParameterName = "@Update_User_id";
            sqlParameter[5].Value = pNews.UPDATE_USER_ID;

            sqlParameter[6] = new SqlParameter();
            sqlParameter[6].SqlDbType = SqlDbType.Image;
            sqlParameter[6].Direction = ParameterDirection.Input;
            sqlParameter[6].ParameterName = "@Image_data";
            sqlParameter[6].Value = pNews.IMAGE_DATA;

            sqlParameter[7] = new SqlParameter();
            sqlParameter[7].SqlDbType = SqlDbType.BigInt;
            sqlParameter[7].Direction = ParameterDirection.Input;
            sqlParameter[7].ParameterName = "@News_id";
            sqlParameter[7].Value = pNews.NEWS_ID;

            sqlCommand.Parameters.AddRange(sqlParameter);
            try
            {
                Utils.sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return false;
            }
            return true;
        }

        public bool UpdateNewsNotImage(News pNews)
        {
            SqlParameter[] sqlParameter = new SqlParameter[7];
            SqlCommand sqlCommand = new SqlCommand(usp_UpdateNewsNotImage, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].SqlDbType = SqlDbType.NVarChar;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@Title";
            sqlParameter[0].Value = pNews.TITLE;

            sqlParameter[1] = new SqlParameter();
            sqlParameter[1].SqlDbType = SqlDbType.NText;
            sqlParameter[1].Direction = ParameterDirection.Input;
            sqlParameter[1].ParameterName = "@Content_desc";
            sqlParameter[1].Value = pNews.CONTENT_DESC;

            sqlParameter[2] = new SqlParameter();
            sqlParameter[2].SqlDbType = SqlDbType.NText;
            sqlParameter[2].Direction = ParameterDirection.Input;
            sqlParameter[2].ParameterName = "@Content_detail";
            sqlParameter[2].Value = pNews.CONTENT_DETAIL;

            sqlParameter[3] = new SqlParameter();
            sqlParameter[3].SqlDbType = SqlDbType.BigInt;
            sqlParameter[3].Direction = ParameterDirection.Input;
            sqlParameter[3].ParameterName = "@Group_id";
            sqlParameter[3].Value = pNews.GROUP_ID;

            sqlParameter[4] = new SqlParameter();
            sqlParameter[4].SqlDbType = SqlDbType.Char;
            sqlParameter[4].Direction = ParameterDirection.Input;
            sqlParameter[4].ParameterName = "@IsHotnews";
            sqlParameter[4].Value = pNews.ISHOTNEWS;

            sqlParameter[5] = new SqlParameter();
            sqlParameter[5].SqlDbType = SqlDbType.Int;
            sqlParameter[5].Direction = ParameterDirection.Input;
            sqlParameter[5].ParameterName = "@Update_User_id";
            sqlParameter[5].Value = pNews.UPDATE_USER_ID;            

            sqlParameter[6] = new SqlParameter();
            sqlParameter[6].SqlDbType = SqlDbType.BigInt;
            sqlParameter[6].Direction = ParameterDirection.Input;
            sqlParameter[6].ParameterName = "@News_id";
            sqlParameter[6].Value = pNews.NEWS_ID;

            sqlCommand.Parameters.AddRange(sqlParameter);
            try
            {
                Utils.sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return false;
            }
            return true;
        }
    }
}
